﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LinqToExcel;
using LinqToExcel.Query;
using System.Globalization;

namespace TPA.DataAccess
{
    public class ProgramLogReader
    {
        
        /// <summary>
        /// Retrieve data from program log excel file.
        /// The sequence of columns in the source excel file must be:
        /// [ProgramName],[Channel],[Date],[StartTime],[EndTime],[AudienceMeasurement],[MarketShare]
        /// </summary>
        /// <param name="excelfilename">excel file path and filename</param>
        /// <returns>List of rows</returns>
        public List<LinqToExcel.Row> GetProgramLog(string excelfilename, string sheetname)
        {
            var excel = new ExcelQueryFactory(excelfilename);
            var programs = from p in excel.Worksheet(sheetname)
                           select p;
            return programs.ToList();
        }

        /// <summary>
        /// Generate program key points
        /// </summary>
        /// <param name="excelfilename">program log file</param>
        ///  <param name="processed">false::[Channel] before [Date]; true::[Date] before [Channel]</param>
        /// <returns></returns>
        public List<KeyPoint> GetProgramLogKeyPoint(string excelfilename, string sheetname, bool processed)
        {
            List<Row> excelrowslist = GetProgramLog(excelfilename, sheetname);
            List<KeyPoint> result = new List<KeyPoint>();
            CultureInfo culture;
            culture=CultureInfo.CreateSpecificCulture("en-US");
            //DateTimeStyles styles=System.Globalization.DateTimeStyles.None;
            for (int i=0;i<excelrowslist.Count;i++)
            {
                Row r=excelrowslist[i];
                //Check row's validation.
                if (r[0].Value.ToString() != "1111")
                {
                    System.DateTime _Date;//, _StartTime, _EndTime;
                    decimal _AudienceMeasurement, _MarketShare;
                    if (!processed)
                    {
                        if (!string.IsNullOrEmpty(r[1].Value.ToString())
                            && (DateTime.TryParse(r[2].Value.ToString(), out  _Date) || DateTime.TryParse(r[2].Value.ToString(), culture, DateTimeStyles.NoCurrentDateDefault, out _Date))
                            && decimal.TryParse(r[5].Value.ToString(), out _AudienceMeasurement)
                            && decimal.TryParse(r[6].Value.ToString(), out _MarketShare))
                        {
                            //Create a new lkp object and add to list.
                            KeyPoint lkp = new KeyPoint()
                            {
                                Label = r[0].Value.ToString().Trim(),
                                Channel = r[1].Value.ToString(),
                                Type = KeyPointType.Program
                            };
                            //Handling datetime.
                            lkp.DateTimeIn = ConvertToDateTime(_Date.ToShortDateString(), r[3].Value.ToString());
                            lkp.DateTimeOut = ConvertToDateTime(_Date.ToShortDateString(), r[4].Value.ToString());
                            lkp.AudienceMeasurement = _AudienceMeasurement;//decimal.Parse(r[5].Value.ToString());
                            lkp.MarketShare = _MarketShare;//decimal.Parse(r[6].Value.ToString());
                            #region Insert new KeyPoint -- need 2 copies
                            if (lkp.Label == "End of Transmission" || lkp.Label == "Start of Transmission")
                            {
                                lkp.Priority = true;
                                //throw (new TPAException() { Source = lkp.Label});
                            }
                            if (result.Count > 0)
                            {
                                //remove same lkp
                                if (!lkp.Equals(result[result.Count - 1]))
                                {

                                    if (lkp.Label == "Start of Transmission")
                                    {
                                        int n = 0;
                                        int count = result.Count;
                                        for (n = 0; (n + 1) < count && result.ElementAt(count - n - 1).DateTimeIn == lkp.DateTimeIn; n++)
                                        {
                                            //
                                        }
                                        if (n == 0)
                                        {
                                            result.Add(lkp);
                                        }
                                        else
                                        {
                                            result.Insert(count - n, lkp);
                                        }
                                    }
                                    else if (lkp.Label == "End of Transmission")
                                    {
                                        result.Add(lkp);
                                    }
                                    else if (lkp.DateTimeIn < result[result.Count - 1].DateTimeOut && (result[result.Count - 1].Label != "End of Transmission" || result[result.Count - 1].Label != "Start of Transmission"))
                                    {
                                        result[result.Count - 1].DateTimeOut = lkp.DateTimeIn;
                                        result.Add(lkp);
                                    }
                                    else if (lkp.DateTimeIn > result[result.Count - 1].DateTimeOut)
                                    {
                                        result.Add(new KeyPoint()
                                        {
                                            Label = "技术调试",
                                            Channel = lkp.Channel,
                                            Type = KeyPointType.Tech,
                                            DateTimeIn = result[result.Count - 1].DateTimeOut,
                                            DateTimeOut = lkp.DateTimeIn,
                                            AudienceMeasurement = 0,
                                            MarketShare = 0,
                                            Priority = false
                                        });
                                        result.Add(lkp);
                                    }
                                    else if (result[result.Count - 1].Label == "End of Transmission" && lkp.DateTimeIn == result[result.Count - 1].DateTimeIn)
                                    {
                                        //do nothing
                                    }
                                    else
                                    {
                                        result.Add(lkp);
                                    }
                                }
                            }
                            else
                            {
                                result.Add(lkp);
                            }
                            #endregion 
                        }
                        else
                        {
                            string values = "";
                            foreach (Cell c in r.ToList())
                            {
                                values += "[" + c.Value + "]";
                            }
                            throw (new TPAException() { RowIndex = i, Source = "Row " + i.ToString() + " has invalid value in cells.\n\rValues:"+values });
                        }
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(r[0].Value.ToString())
                            && !string.IsNullOrEmpty(r[2].Value.ToString())
                            && (DateTime.TryParse(r[1].Value.ToString(), out  _Date) || DateTime.TryParse(r[1].Value.ToString(), culture, DateTimeStyles.NoCurrentDateDefault, out _Date))
                            && decimal.TryParse(r[5].Value.ToString(), out _AudienceMeasurement)
                            && decimal.TryParse(r[6].Value.ToString(), out _MarketShare))
                        {
                            //Create a new lkp object and add to list.
                            KeyPoint lkp = new KeyPoint()
                            {
                                Label = r[0].Value.ToString(),
                                Channel = r[2].Value.ToString(),
                                Type = KeyPointType.Program
                            };
                            //Handling datetime.
                            lkp.DateTimeIn = ConvertToDateTime(_Date.ToShortDateString(), r[3].Value.ToString());
                            lkp.DateTimeOut = ConvertToDateTime(_Date.ToShortDateString(), r[4].Value.ToString());
                            lkp.AudienceMeasurement = _AudienceMeasurement;//decimal.Parse(r[5].Value.ToString());
                            lkp.MarketShare = _MarketShare;//decimal.Parse(r[6].Value.ToString());
                            #region Insert new KeyPoint  -- need 2 copies
                            if (lkp.Label == "End of Transmission" || lkp.Label == "Start of Transmission")
                            {
                                lkp.Priority = true;
                                //throw (new TPAException() { Source = lkp.Label});
                            }
                            if (result.Count > 0)
                            {
                                //remove same lkp
                                if (!lkp.Equals(result[result.Count - 1]))
                                {

                                    if (lkp.Label == "Start of Transmission")
                                    {
                                        int n = 0;
                                        int count = result.Count;
                                        for (n = 0; (n + 1) < count && result.ElementAt(count - n - 1).DateTimeIn == lkp.DateTimeIn; n++)
                                        {
                                            //
                                        }
                                        if (n == 0)
                                        {
                                            result.Add(lkp);
                                        }
                                        else
                                        {
                                            result.Insert(count - n, lkp);
                                        }
                                    }
                                    else if (lkp.Label == "End of Transmission")
                                    {
                                        result.Add(lkp);
                                    }
                                    else if (lkp.DateTimeIn < result[result.Count - 1].DateTimeOut && (result[result.Count - 1].Label != "End of Transmission" && result[result.Count - 1].Label != "Start of Transmission"))
                                    {
                                        result[result.Count - 1].DateTimeOut = lkp.DateTimeIn;
                                        result.Add(lkp);
                                    }
                                    else if (lkp.DateTimeIn > result[result.Count - 1].DateTimeOut)
                                    {
                                        result.Add(new KeyPoint()
                                        {
                                            Label = "技术调试",
                                            Channel = lkp.Channel,
                                            Type = KeyPointType.Tech,
                                            DateTimeIn = result[result.Count - 1].DateTimeOut,
                                            DateTimeOut = lkp.DateTimeIn,
                                            AudienceMeasurement = 0,
                                            MarketShare = 0,
                                            Priority = false
                                        });
                                        result.Add(lkp);
                                    }
                                    else if (result[result.Count - 1].Label == "End of Transmission"&&lkp.DateTimeIn==result[result.Count - 1].DateTimeIn)
                                    {
                                        //do nothing
                                        string x = result[result.Count - 1].Label;
                                    }
                                    else
                                    {
                                        result.Add(lkp);
                                    }
                                }
                            }
                            else
                            {
                                result.Add(lkp);
                            }
                            #endregion 
                        }
                        else
                        {
                            string values = "";
                            foreach (Cell c in r.ToList())
                            {
                                values += "[" + c.Value + "]";
                            }
                            throw (new TPAException() { RowIndex = i, Source = "Row " + i.ToString() + " has invalid value in cells.\n\rValues:" + values });
                        }
                    }
                }
            }
            return result;
        }

        //private LogKeyPoint ConvertToLogKeyPoint(Row r)
        
        /// <summary>
        /// Combine a date string and a time string
        /// </summary>
        /// <param name="date">valid date string</param>
        /// <param name="time">valid time string</param>
        /// <returns>DateTime</returns>
        private DateTime ConvertToDateTime(string date, string time)
        {
            DateTime result, _time;
            result = DateTime.Parse(date);
            if (time.Length <= 8 && time.IndexOf(":")>0)
            {
                TimeSpan ts = Within24Hours(time);
                if (ts.TotalSeconds == 0)
                {
                    result = result.AddSeconds(CalcSeconds(time));
                }
                else
                {
                    result = result.AddDays(1);
                    result = result.AddSeconds(ts.TotalSeconds);
                }
            }
            else
            {
                if (DateTime.TryParse(time, out _time))
                {
                    if (_time.Year == 1899||_time.Year==DateTime.Now.Year)
                    {
                        result = result.AddSeconds(_time.TimeOfDay.TotalSeconds);
                    }
                    else
                    {
                        result = result.AddDays(1);
                        result = result.AddSeconds(_time.TimeOfDay.TotalSeconds);
                    }
                }
                else
                {
                    throw new TPAException() { Source = "DateTime String is not valid: " + time };
                }
            }
            return result;
        }

        /// <summary>
        /// Calculate the total seconds of a time string
        /// </summary>
        /// <param name="time">valid time string, eg: hh:mm:ss</param>
        /// <returns>total seconds</returns>
        private int CalcSeconds(string time)
        {
            string[] x = time.Split(':');
            int h = int.Parse(x[0]);
            int m = int.Parse(x[1]);
            int s = int.Parse(x[2]);
            return  h * 3600 + m * 60 + s;
        }

        private int CalcDuration(string duration)
        {
            string[] x = duration.Split(':');
            int m = int.Parse(x[0]);
            int s=int.Parse(x[1]);
            return m*60+s;
        }

        /// <summary>
        /// Compare a time string with 24 hours. If a time is within 24 hours, then return 0; 
        /// else return the time span between the input time and 24 hours. Commonly, its a positive number.
        /// </summary>
        /// <param name="time">valid time string</param>
        /// <returns>time span</returns>
        private TimeSpan Within24Hours(string time)
        {
            int dailyseconds = 59 + 59 * 60 + 23 * 3600;
            int seconds = CalcSeconds(time);
            if (seconds <= dailyseconds)
                return TimeSpan.FromSeconds(0);
            else
                return TimeSpan.FromSeconds(seconds - dailyseconds-1);
        }

        /// <summary>
        /// Retrieve data from advertisement log excel file.
        /// The sequence of columns in the source excel file must be:
        /// [ProgramName],[Date],[Channel],[StartTime],[Duration],[AudienceMeasurement],[MarketShare]
        /// </summary>
        /// <param name="excelfilename">excel file path and filename</param>
        /// <returns>List of rows</returns>
        public List<LinqToExcel.Row> GetAdvertisementLog(string excelfilename, string sheetname)
        {
            var excel = new ExcelQueryFactory(excelfilename);
            var programs = from p in excel.Worksheet(sheetname)
                           select p;
            return programs.ToList();
        }

        /// <summary>
        /// Generate program key points
        /// </summary>
        /// <param name="excelfilename">program log file</param>
        ///  <param name="sheetindex">sheet index</param>
        /// <returns></returns>
        public List<KeyPoint> GetAdvertisementLogKeyPoint(string excelfilename, string sheetname)
        {
            List<Row> excelrowslist = GetAdvertisementLog(excelfilename, sheetname);
            List<KeyPoint> result = new List<KeyPoint>();
            for (int i=0;i<excelrowslist.Count;i++)
            {
                Row r=excelrowslist[i];
                //Check row's validation.
                System.DateTime _Date;//, _StartTime;//, _EndTime;
                decimal _AudienceMeasurement, _MarketShare;
                if (r[0].Value.ToString() != "1111")
                {

                    if (!string.IsNullOrEmpty(r[0].Value.ToString())
                        && !string.IsNullOrEmpty(r[2].Value.ToString())
                        && (DateTime.TryParse(r[1].Value.ToString(), out  _Date) || DateTime.TryParse(r[1].Value.ToString(), CultureInfo.CreateSpecificCulture("en-US"), DateTimeStyles.NoCurrentDateDefault, out _Date))
                        //&& DateTime.TryParse(r[3].Value.ToString(), out _StartTime)
                        && decimal.TryParse(r[5].Value.ToString(), out _AudienceMeasurement)
                        && decimal.TryParse(r[6].Value.ToString(), out _MarketShare))
                    {
                        KeyPoint lkp = new KeyPoint()
                        {
                            Label = r[0].Value.ToString(),
                            Channel = r[2].Value.ToString(),
                            Type = KeyPointType.Advertisement
                        };

                        lkp.DateTimeIn = ConvertToDateTime(_Date.ToShortDateString(), r[3].Value.ToString());
                        lkp.DateTimeOut = lkp.DateTimeIn.AddSeconds(CalcDuration(r[4].Value.ToString()));
                        if (i > 0)
                        {
                            if (lkp.DateTimeIn < result.ElementAt(i - 1).DateTimeOut)
                            {
                                lkp.DateTimeIn=result.ElementAt(i - 1).DateTimeOut ;
                            }
                        }
                        lkp.AudienceMeasurement = _AudienceMeasurement;// decimal.Parse(r[5].Value.ToString());
                        lkp.MarketShare = _MarketShare;// decimal.Parse(r[6].Value.ToString());
                        if (result.Count > 0)
                        {
                            if (!lkp.Equals(result[result.Count - 1]))
                            {
                                result.Add(lkp);
                            }
                            else
                            {
                                result.Remove(result.Last());
                                result.Add(lkp);
                            }
                                
                        }
                        else
                        {
                            result.Add(lkp);
                        }
                    }
                    else
                    {
                        string values = "";
                        foreach (Cell c in r.ToList())
                        {
                            values += "[" + c.Value + "]";
                        }
                        throw (new TPAException() { RowIndex = i, Source = "Row " + i.ToString() + " has invalid value in cells.\n\rValues:" + values });
                    }
                }
            }
            return result;
        }
    }
}
